package com.danco.training.zaneuskaya.connection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.danco.training.zaneuskaya.connection.dao.AbstractDao;
import com.danco.training.zaneuskaya.connection.dao.BookDao;
import com.danco.training.zaneuskaya.domain.Book;


public class BookDaoImpl extends AbstractDao<Book, Integer> implements BookDao{
	
	public static String UPDATE_QUERY = "UPDATE books SET TITLE = ?, AUTHOR = ?, PUB_DATE = ?, PRICE = ? ,AVAILABLE = ?,QUANTITY = ?, REC_DATE = ?  WHERE B_ID = ?" ;
	public static String insertQuery = "INSERT INTO books (TITLE, AUTHOR, PUB_DATE, PRICE,AVAILABLE,QUANTITY, REC_DATE) VALUES (?,?,?,?,?,?,?)";
	public static String lastInsertedQuery = "SELECT * FROM books WHERE B_ID = LAST_INSERT_ID()";
	public static String readQuery = "SELECT * FROM books WHERE B_ID = ?";
	public static String readAllQuery = "SELECT * FROM books";
	public static String deleteQuery = "DELETE FROM books WHERE B_ID = ?";
	public static String someMonthsUnsoldBooksQuery = "SELECT * FROM books WHERE (REC_DATE - CURDATE() = ?)";
	public static String sortedUnsoldBooksByPriceQuery = "SELECT * FROM books WHERE (MONTH(REC_DATE) - MONTH(CURDATE()) = ?) ORDER BY PRICE";
	public static String sortedUnsoldBooksByDateQuery = "SELECT * FROM books WHERE (MONTH(REC_DATE) - MONTH(CURDATE()) = ?) ORDER BY REC_DATE";
	public static String sortedBooksByTitle = "SELECT * FROM books ORDER BY TITLE ";
	public static String sortedBooksByPublicationDate = "SELECT * FROM books ORDER BY PUB_DATE ";
	public static String sortedBooksByPrice = "SELECT * FROM books ORDER BY PRICE ";
	public static String sortedBooksByAvailable = "SELECT * FROM books ORDER BY AVAILABLE ";
	public static String availableBooksQuery = "SELECT * FROM books WHERE AVAILABLE = ?;";

	private static final Logger logger = Logger
			.getLogger(BookDaoImpl.class);
	
	@Override
	public Book convertResultSet(ResultSet resultSet) throws SQLException {
		
		
		int id = resultSet.getInt("B_ID");
		String title = resultSet.getString("TITLE");
		String author = resultSet.getString("AUTHOR");
		Date publicationDate = resultSet.getDate("PUB_DATE");
		double price = resultSet.getDouble("PRICE");
		int available = resultSet.getInt("AVAILABLE");
		boolean av = false;
		if (available == 1){
			av = true;
		}
		int quantity = resultSet.getInt("QUANTITY");
		Date receiptDate = resultSet.getDate("REC_DATE");
		Book book = new Book(id,title,author,publicationDate,
				price,av,quantity,receiptDate);
		return book;
	}

	@Override
	public boolean existsInDB(Connection connection, Book object) throws SQLException {
		if ( read(connection, object.getId()) != null){
			return true;
		}
		return false;
	}

	@Override
	public PreparedStatement getUpdateStatement(Connection c, Book object) throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(UPDATE_QUERY);
		preparedStatement.setString(1, object.getTitle());
		preparedStatement.setString(2, object.getAuthor());
		preparedStatement.setDate(3, new java.sql.Date( object.getPublicationDate().getTime()));
		preparedStatement.setDouble(4, object.getPrice());
		int av = 0;
		if (object.isAvailable()){
			av = 1;
		}
		preparedStatement.setInt(5, av);
		preparedStatement.setInt(6, object.getQuantity());
		preparedStatement.setDate(7, new java.sql.Date( object.getReceiptDate().getTime()));
		preparedStatement.setInt(8, object.getId());
		
		return preparedStatement;
	}

	@Override
	public PreparedStatement getInsertStatement(Connection c, Book object) throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(insertQuery);
		preparedStatement.setString(1, object.getTitle());
		preparedStatement.setString(2, object.getAuthor());
		preparedStatement.setDate(3,new java.sql.Date(  object.getPublicationDate().getTime()));
		preparedStatement.setDouble(4, object.getPrice());
		int av = 0;
		if (object.isAvailable()){
			av = 1;
		}
		preparedStatement.setInt(5, av);
		preparedStatement.setInt(6, object.getQuantity());
		preparedStatement.setDate(7, new java.sql.Date(object.getReceiptDate().getTime()));
		return preparedStatement;
	}

	@Override
	public PreparedStatement getLastInsertedStatement(Connection c) throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(lastInsertedQuery);
		return preparedStatement;
	}

	@Override
	public PreparedStatement readStatement(Connection c, Integer PK) throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(readQuery);
		preparedStatement.setInt(1, PK);
		return preparedStatement;
	}

	@Override
	public PreparedStatement readAllStatement(Connection c) throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(readAllQuery);
		return preparedStatement;
	}

	@Override
	public PreparedStatement getDeleteStatement(Connection c, Book object) throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(deleteQuery);
		preparedStatement.setInt(1, object.getId());
		return preparedStatement;
	}

	@Override
	public List<Book> readSomeMonthsUnsoldBooks(Connection connection, int months) {
		List<Book> books = new ArrayList<Book>();
		try( 
			 PreparedStatement preparedStatement = connection.prepareStatement(someMonthsUnsoldBooksQuery);	
				){
			preparedStatement.setInt(1, months);
			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				books.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return books;
		
		
	}

	@Override
	public List<Book> readBooksSortedByAlphabet(Connection connection) {
		List<Book> books = new ArrayList<Book>();
		try( 
			 PreparedStatement preparedStatement = connection.prepareStatement(sortedBooksByTitle);	
				){

			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				books.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return books;
		
	}

	@Override
	public List<Book> readBooksSortedByPublicationDate(Connection connection) {
		List<Book> books = new ArrayList<Book>();
		try( 
			 PreparedStatement preparedStatement = connection.prepareStatement(sortedBooksByPublicationDate);	
				){
			
			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				books.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return books;
	}

	@Override
	public List<Book> readBooksSortedByPrice(Connection connection) {
		List<Book> books = new ArrayList<Book>();
		try( 
			 PreparedStatement preparedStatement = connection.prepareStatement(sortedBooksByPrice);	
				){
			
			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				books.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return books;
	}

	@Override
	public List<Book> readBooksSortedByAvailability(Connection connection) {
		List<Book> books = new ArrayList<Book>();
		try(
			 PreparedStatement preparedStatement = connection.prepareStatement(sortedBooksByAvailable);	
				){
			
			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				books.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return books;
	}

	@Override
	public List<Book> readUnsoldBooksSortedByReceiptDate(Connection connection, int months) {
		List<Book> books = new ArrayList<Book>();
		try( 
			 PreparedStatement preparedStatement = connection.prepareStatement(sortedUnsoldBooksByDateQuery);	
				){
			preparedStatement.setInt(1, months);

			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				books.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return books;
	}

	@Override
	public List<Book> readUnsoldBooksSortedByPrice(Connection connection, int months) {
		List<Book> books = new ArrayList<Book>();
		try( 
			 PreparedStatement preparedStatement = connection.prepareStatement(sortedUnsoldBooksByPriceQuery);	
				){
			preparedStatement.setInt(1, months);
		
			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				books.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return books;
	}


	@Override
	public PreparedStatement getDeleteByIdStatement(Connection c, Integer PK)
			throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(deleteQuery);
		preparedStatement.setInt(1, PK);
		return preparedStatement;
	}

	

	@Override
	public List<Book> readAvailableBooks(Connection connection) {
		List<Book> books = new ArrayList<Book>();
		try( 
			 PreparedStatement preparedStatement = connection.prepareStatement(availableBooksQuery);	
				){
			preparedStatement.setInt(1, 1);
			
			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				books.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return books;
	}

	@Override
	public List<Book> readNotAvailableBooks(Connection connection) {
		List<Book> books = new ArrayList<Book>();
		try( 
			 PreparedStatement preparedStatement = connection.prepareStatement(availableBooksQuery);	
				){
			preparedStatement.setInt(1, 0);
			
			ResultSet result = preparedStatement.executeQuery();
			while (result.next()){
				books.add(convertResultSet(result));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return books;
	}

	
	
	
}
